package cn.my_server.z_transSQL;


import java.util.ArrayList;
import java.util.List;

public class SQLFunction {

    /**
     * 错误提示
     */
    private String errMsg = "输入的SQL语句或是参数不正确,请检查重新输入! \n" +
            "SQL示例: select * from tabName where a=? and b=? and c=? and d=? \n" +
            "参数示例: a, b, c, d 或者  [a, b, c, d] 或者  00000(String), 00001(String)";

    /**
     * sql语句处理块
     *
     * @param sql
     * @param pars
     * @return
     */
    public String dealSQLwithPara(String sql, String pars) {
        String result = "";
        try {
            boolean conds = false;
            if (sql == "") conds = true;
            if (sql == null) conds = true;
            if (pars == "") conds = true;
            if (pars == null) conds = true;
            if (pars.equals("")) conds = true;

            if (conds) {
                result = errMsg;
            } else {
                StringBuffer sqlString = new StringBuffer(sql);
                StringBuffer parString = new StringBuffer(pars.trim());

                List<String> parStr = new ArrayList<String>();
                String[] parStrs = null;
                String[] parStrs1 = null;
                String[] parStrs2 = null;
                String[] parStrs3 = null;

                if (parString.toString().endsWith("]")) {
                    parString.delete(parString.length() - 1, parString.length());
                }

                if (parString.toString().startsWith("[")) {
                    parString.delete(0, 1);
                }

                String sp1 = ", ";
                String sp2 = " +";
                String sp3 = ",";
                parStrs1 = parString.toString().split(sp1);
                parStrs2 = parString.toString().split(sp2);
                parStrs3 = parString.toString().split(sp3);

                if (parStrs1.length >= parStrs2.length && parStrs1.length >= parStrs3.length) {
                    parStrs = parStrs1;
                } else if (parStrs2.length >= parStrs1.length && parStrs2.length >= parStrs3.length) {
                    parStrs = parStrs2;
                } else if (parStrs3.length >= parStrs2.length && parStrs3.length >= parStrs1.length) {
                    parStrs = parStrs3;
                }

                for (String str : parStrs) {
                    if (str.indexOf("(") != -1) {
                        parStr.add(str.substring(0, str.indexOf("(")));
                    } else {
                        parStr.add(str.substring(0, str.length()));
                    }
                }

                for (String str : parStr) {

                    if (str.endsWith(",")) {
                        str = str.trim();
                        sqlString.replace(sqlString.indexOf("?"), sqlString.indexOf("?") + 1, "'" + str.substring(0, str.length() - 1) + "'");
                    } else {
                        str = str.trim();
                        sqlString.replace(sqlString.indexOf("?"), sqlString.indexOf("?") + 1, "'" + str + "'");
                    }
                }
                result = sqlString.toString();
                if (result.toUpperCase().contains("LIMIT")) {
					result = sqlString.deleteCharAt(sqlString.lastIndexOf("'"))
                            .deleteCharAt(sqlString.lastIndexOf("'"))
                            .deleteCharAt(sqlString.lastIndexOf("'"))
                            .deleteCharAt(sqlString.lastIndexOf("'"))
                            .toString();
                }
            }

        } catch (Exception e) {
            result = e.getMessage() + "\n" + errMsg;
        }
        return result;
    }

    public String showExamples() {
        return errMsg;
    }
}

